Various methods of managing collections of data (e.g., databases) have been developed since data was first stored in electronic form. From initial systems and applications that simply collected data in one or more flat database files to present sophisticated database management systems (DBMS), different solutions have been developed to meet different requirements. Early solutions may have had the advantage of simplicity but became obsolete for a variety of factors, such as the need to store large, even vast, quantities of data, a desire for more sophisticated search and/or retrieval techniques (e.g., based on relationships between data), and the need to store different types of data (e.g., audio, visual), etc.
A database management system, or DBMS, gives the user access to his data and helps him transform the data into information. A DBMS allows users to create, update, and extract information from his databases. Compared to a manual filing system, the biggest advantages to a computerized database system are speed, accuracy, and accessibility.
A hierarchical database is a kind of database management system that links records together in a tree data structure such that each record type has only one owner, e.g. an order is owned by only one customer. A relational database can be seen as the data handling part of another application. The application instructs the database to perform searches, as well as add, delete and modify data via the Structured Query Language or SQL.
Various techniques have been used to implement a hierarchical tree structure with relational databases. These techniques can be used for implementing as well as updating a tree structure. For example, the adjacent list technique can be used, where each tree node in a tree structure stores a pointer to its parent. This technique however, is not efficient because a user cannot retrieve children of a node over multiple levels with one standard SQL statement. To get the grandchild, you first have to select the children, and with a second SQL select, the children of the children. Another technique, the string set, where the hierarchy is mapped to a string field can be used. However, this technique is not useful because the length of the string of each node implies a fixed tree depth and modification in the tree structure requires complicated database operations.
Accordingly, there is a need for improved systems and methods for efficiently implementing tree structures with relational databases.